package day1130book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import day1130.JDBCUtil;

public class BookDao {
	//添加书本信息到数据库
	public static void insert(Book b) {
		String sql = "insert into book(name,status,count) values(?,?,?)";
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, b.getName());
			ps.setString(2, b.getStatus());
			ps.setInt(3, b.getCount());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
	}
	
	//修改信息,借书
	public static int update(Book b) {
		String sql = "update book set count = ?,status = ? where name = ?";
		
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ArrayList<Book> list = BookDao.select(b);
			
			int count = list.get(0).getCount()+1;
			ps.setInt(1, count);
			ps.setString(2, "off");
			ps.setString(3, b.getName());
			int res = ps.executeUpdate();
			return res;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return 0;
	}
	
	//修改信息,还书
		public static int update1(Book b) {
			String sql = "update book set status = ? where name = ?";
			
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				conn = JDBCUtil.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "on");
				ps.setString(2, b.getName());
				int res = ps.executeUpdate();
				return res;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				JDBCUtil.close(conn, ps);
			}
			return 0;
		}
		
	//根据书名查询书的信息
	public static ArrayList<Book> select(Book b) {
		ArrayList<Book> list = new ArrayList<>();
		List<Object> vals = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from book where 1=1 ");
		PreparedStatement ps = null;
		ResultSet result = null;
		String name = b.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name = ?");
			vals.add(name);
		}
		
		String status = b.getStatus();
		if(status!=null&&!status.trim().isEmpty()) {
			sql.append(" and status = ?");
			vals.add(status);
		}
		
		Integer count = b.getCount();
		if(count!=null) {
			sql.append(" and count = ?");
			vals.add(status);
		}
		
		try {
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size(); i++) {
				ps.setObject(i+1, vals.get(i));
			}
			result = ps.executeQuery();
			while(result.next()) {
				Book bb = new Book();
				bb.setName(result.getString("name"));
				bb.setStatus(result.getString("status"));
				bb.setCount(result.getInt("count"));
				list.add(bb);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
}
